﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.IO;
using System.Windows.Forms;
using System.Data;

namespace WHC.OrderWater.Commons
{
    public class SimpleExcelExport
    {
        /// <summary>
        /// 将DataTable导出为Excel
        /// </summary>
        public static void Output(DataTable dataTable)
        {
            Output(dataTable, DateTime.Now.ToString("yyyyMMddHHmmssfff"));
        }

        /// <summary>
        /// 将DataTable到处为Excel
        /// </summary>
        /// <param name="dataTable">表</param>
        /// <param name="name">导出默认文件名</param>
        public static void Output(DataTable dataTable, string name)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            string fileName = "";
            saveFileDialog.Filter = "xls files (*.xls)|*.xls";
            saveFileDialog.FileName = name;
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                fileName = saveFileDialog.FileName;
                if (File.Exists(fileName))
                {
                    try
                    {
                        File.Delete(fileName);
                    }
                    catch
                    {
                        MessageBox.Show("该文件正在使用中,关闭文件或重新命名导出文件再试!");
                        return;
                    }
                }
                OleDbConnection oleDbConn = new OleDbConnection();
                OleDbCommand oleDbCmd = new OleDbCommand();
                string sSql = "";
                try
                {
                    oleDbConn.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + fileName + @";Extended ProPerties=""Excel 8.0;HDR=Yes;""";
                    oleDbConn.Open();
                    oleDbCmd.CommandType = CommandType.Text;
                    oleDbCmd.Connection = oleDbConn;
                    sSql = "CREATE TABLE sheet1 (";
                    for (int i = 0; i < dataTable.Columns.Count; i++)
                    {
                        // 字段名称出现关键字会导致错误。
                        if (i < dataTable.Columns.Count - 1)
                            sSql += "[" + dataTable.Columns[i].Caption + "] TEXT(100) ,";
                        else
                            sSql += "[" + dataTable.Columns[i].Caption + "] TEXT(200) )";
                    }
                    oleDbCmd.CommandText = sSql;
                    oleDbCmd.ExecuteNonQuery();
                    for (int j = 0; j < dataTable.Rows.Count; j++)
                    {
                        sSql = "INSERT INTO sheet1 VALUES('";
                        for (int i = 0; i < dataTable.Columns.Count; i++)
                        {
                            if (i < dataTable.Columns.Count - 1)
                                sSql += dataTable.Rows[j][i].ToString() + " ','";
                            else
                                sSql += dataTable.Rows[j][i].ToString() + " ')";
                        }
                        oleDbCmd.CommandText = sSql;
                        oleDbCmd.ExecuteNonQuery();
                    }
                    MessageBox.Show("导出EXCEL成功");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("导出EXCEL失败:" + ex.Message);
                }
                finally
                {
                    oleDbCmd.Dispose();
                    oleDbConn.Close();
                    oleDbConn.Dispose();
                }
            }
        }
    }
}
